"""
企业级金融数据MCP服务系统 - 期货数据模型
提供完整的期货相关数据SQLAlchemy ORM模型定义
"""

from datetime import datetime, date
from decimal import Decimal
from typing import Optional, Dict, Any
from sqlalchemy import String, DateTime, Date, Boolean, Integer, Numeric, Text, Index, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.dialects.postgresql import JSONB

from .base import BaseFinancialModel, DataVersionMixin, SyncStatusMixin, PartitionMixin


class FuturesBasicInfo(BaseFinancialModel, SyncStatusMixin):
    """
    期货基本信息模型
    存储期货合约的基础信息，如合约代码、名称、交易所等
    """
    __tablename__ = "futures_basic_info"
    
    # 期货合约代码
    contract_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="期货合约代码")
    
    # 期货合约名称
    contract_name: Mapped[str] = mapped_column(String(100), nullable=False, comment="期货合约名称")
    
    # 品种代码
    variety_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="品种代码")
    
    # 品种名称
    variety_name: Mapped[str] = mapped_column(String(100), nullable=False, comment="品种名称")
    
    # 交易所代码：SHFE(上期所), DCE(大商所), CZCE(郑商所), CFFEX(中金所), INE(上期能源)
    exchange: Mapped[str] = mapped_column(String(10), nullable=False, comment="交易所代码")
    
    # 合约类型：商品期货, 金融期货, 股指期货, 国债期货等
    contract_type: Mapped[Optional[str]] = mapped_column(String(50), nullable=True, comment="合约类型")
    
    # 交易单位
    trade_unit: Mapped[Optional[str]] = mapped_column(String(50), nullable=True, comment="交易单位")
    
    # 报价单位
    quote_unit: Mapped[Optional[str]] = mapped_column(String(50), nullable=True, comment="报价单位")
    
    # 最小变动价位
    min_price_change: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 6), nullable=True, comment="最小变动价位")
    
    # 涨跌停板幅度(%)
    price_limit: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="涨跌停板幅度(%)")
    
    # 合约月份
    contract_month: Mapped[Optional[str]] = mapped_column(String(10), nullable=True, comment="合约月份")
    
    # 交易时间
    trade_time: Mapped[Optional[str]] = mapped_column(String(200), nullable=True, comment="交易时间")
    
    # 最后交易日
    last_trade_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="最后交易日")
    
    # 交割日期
    delivery_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="交割日期")
    
    # 交割方式：实物交割, 现金交割
    delivery_method: Mapped[Optional[str]] = mapped_column(String(20), nullable=True, comment="交割方式")
    
    # 交割品级
    delivery_grade: Mapped[Optional[str]] = mapped_column(Text, nullable=True, comment="交割品级")
    
    # 交割地点
    delivery_location: Mapped[Optional[str]] = mapped_column(Text, nullable=True, comment="交割地点")
    
    # 保证金比例(%)
    margin_ratio: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="保证金比例(%)")
    
    # 手续费
    commission: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, comment="手续费")
    
    # 上市日期
    list_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="上市日期")
    
    # 退市日期
    delist_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True, comment="退市日期")
    
    # 合约状态：上市, 交易, 交割, 退市
    contract_status: Mapped[str] = mapped_column(String(20), nullable=False, default="上市", comment="合约状态")
    
    # 扩展信息
    extra_info: Mapped[Optional[Dict[str, Any]]] = mapped_column(JSONB, nullable=True, comment="扩展信息")
    
    __table_args__ = (
        UniqueConstraint('contract_code', 'exchange', name='uk_futures_basic_code_exchange'),
        Index('idx_futures_basic_code', 'contract_code'),
        Index('idx_futures_basic_variety', 'variety_code'),
        Index('idx_futures_basic_exchange', 'exchange'),
        Index('idx_futures_basic_type', 'contract_type'),
        Index('idx_futures_basic_month', 'contract_month'),
        Index('idx_futures_basic_last_trade_date', 'last_trade_date'),
        Index('idx_futures_basic_status', 'contract_status'),
    )
    
    def __repr__(self) -> str:
        return f"<FuturesBasicInfo(contract_code={self.contract_code}, variety_name={self.variety_name}, exchange={self.exchange})>"


class FuturesDailyData(BaseFinancialModel, PartitionMixin):
    """
    期货日线数据模型
    存储期货合约的日K线数据，包括开高低收、成交量、持仓量等
    """
    __tablename__ = "futures_daily_data"
    
    # 期货合约代码
    contract_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="期货合约代码")
    
    # 交易日期
    trade_date: Mapped[date] = mapped_column(Date, nullable=False, comment="交易日期")
    
    # 开盘价
    open_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="开盘价")
    
    # 最高价
    high_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="最高价")
    
    # 最低价
    low_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="最低价")
    
    # 收盘价
    close_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="收盘价")
    
    # 结算价
    settle_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="结算价")
    
    # 前结算价
    pre_settle: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="前结算价")
    
    # 涨跌额
    change: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="涨跌额")
    
    # 涨跌幅(%)
    pct_chg: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="涨跌幅(%)")
    
    # 成交量（手）
    volume: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="成交量(手)")
    
    # 成交额（万元）
    amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="成交额(万元)")
    
    # 持仓量（手）
    open_interest: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="持仓量(手)")
    
    # 持仓量变化
    oi_change: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="持仓量变化")
    
    # 仓单量
    warehouse_receipt: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="仓单量")
    
    # 基差（现货价格-期货价格）
    basis: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="基差")
    
    __table_args__ = (
        UniqueConstraint('contract_code', 'trade_date', name='uk_futures_daily_code_date'),
        Index('idx_futures_daily_code', 'contract_code'),
        Index('idx_futures_daily_date', 'trade_date'),
        Index('idx_futures_daily_code_date', 'contract_code', 'trade_date'),
        Index('idx_futures_daily_volume', 'volume'),
        Index('idx_futures_daily_open_interest', 'open_interest'),
        # 分区索引
        Index('idx_futures_daily_partition_date', 'partition_date'),
    )
    
    def __repr__(self) -> str:
        return f"<FuturesDailyData(contract_code={self.contract_code}, trade_date={self.trade_date}, close_price={self.close_price})>"


class FuturesMinuteData(BaseFinancialModel, PartitionMixin):
    """
    期货分钟数据模型
    存储期货合约的分钟级数据
    """
    __tablename__ = "futures_minute_data"
    
    # 期货合约代码
    contract_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="期货合约代码")
    
    # 交易时间
    trade_time: Mapped[datetime] = mapped_column(DateTime, nullable=False, comment="交易时间")
    
    # 开盘价
    open_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="开盘价")
    
    # 最高价
    high_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="最高价")
    
    # 最低价
    low_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="最低价")
    
    # 收盘价
    close_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="收盘价")
    
    # 成交量（手）
    volume: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="成交量(手)")
    
    # 成交额（万元）
    amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(20, 4), nullable=True, comment="成交额(万元)")
    
    # 持仓量（手）
    open_interest: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="持仓量(手)")
    
    __table_args__ = (
        UniqueConstraint('contract_code', 'trade_time', name='uk_futures_minute_code_time'),
        Index('idx_futures_minute_code', 'contract_code'),
        Index('idx_futures_minute_time', 'trade_time'),
        Index('idx_futures_minute_code_time', 'contract_code', 'trade_time'),
        # 分区索引
        Index('idx_futures_minute_partition_date', 'partition_date'),
    )
    
    def __repr__(self) -> str:
        return f"<FuturesMinuteData(contract_code={self.contract_code}, trade_time={self.trade_time}, close_price={self.close_price})>"


class FuturesHoldingRank(BaseFinancialModel, PartitionMixin):
    """
    期货持仓排名数据模型
    存储期货合约的持仓排名信息
    """
    __tablename__ = "futures_holding_rank"
    
    # 期货合约代码
    contract_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="期货合约代码")
    
    # 交易日期
    trade_date: Mapped[date] = mapped_column(Date, nullable=False, comment="交易日期")
    
    # 排名类型：成交量排名, 持买单量排名, 持卖单量排名
    rank_type: Mapped[str] = mapped_column(String(20), nullable=False, comment="排名类型")
    
    # 排名
    rank: Mapped[Integer] = mapped_column(Integer, nullable=False, comment="排名")
    
    # 会员简称
    member_name: Mapped[str] = mapped_column(String(100), nullable=False, comment="会员简称")
    
    # 成交量（手）
    volume: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="成交量(手)")
    
    # 成交量变化
    volume_change: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="成交量变化")
    
    # 持仓量（手）
    holding: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="持仓量(手)")
    
    # 持仓量变化
    holding_change: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="持仓量变化")
    
    __table_args__ = (
        UniqueConstraint('contract_code', 'trade_date', 'rank_type', 'rank', 
                        name='uk_futures_holding_rank_code_date_type_rank'),
        Index('idx_futures_holding_rank_code', 'contract_code'),
        Index('idx_futures_holding_rank_date', 'trade_date'),
        Index('idx_futures_holding_rank_type', 'rank_type'),
        Index('idx_futures_holding_rank_member', 'member_name'),
        # 分区索引
        Index('idx_futures_holding_rank_partition_date', 'partition_date'),
    )
    
    def __repr__(self) -> str:
        return f"<FuturesHoldingRank(contract_code={self.contract_code}, trade_date={self.trade_date}, rank_type={self.rank_type}, rank={self.rank})>"


class FuturesWarehouseReceipt(BaseFinancialModel, PartitionMixin):
    """
    期货仓单数据模型
    存储期货品种的仓单信息
    """
    __tablename__ = "futures_warehouse_receipt"
    
    # 品种代码
    variety_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="品种代码")
    
    # 统计日期
    stat_date: Mapped[date] = mapped_column(Date, nullable=False, comment="统计日期")
    
    # 仓库名称
    warehouse_name: Mapped[str] = mapped_column(String(200), nullable=False, comment="仓库名称")
    
    # 昨日仓单量
    yesterday_receipt: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="昨日仓单量")
    
    # 今日仓单量
    today_receipt: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="今日仓单量")
    
    # 仓单变化
    receipt_change: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="仓单变化")
    
    # 有效预报
    valid_forecast: Mapped[Optional[Integer]] = mapped_column(Integer, nullable=True, comment="有效预报")
    
    __table_args__ = (
        UniqueConstraint('variety_code', 'stat_date', 'warehouse_name', 
                        name='uk_futures_warehouse_receipt_variety_date_warehouse'),
        Index('idx_futures_warehouse_receipt_variety', 'variety_code'),
        Index('idx_futures_warehouse_receipt_date', 'stat_date'),
        Index('idx_futures_warehouse_receipt_warehouse', 'warehouse_name'),
        # 分区索引
        Index('idx_futures_warehouse_receipt_partition_date', 'partition_date'),
    )
    
    def __repr__(self) -> str:
        return f"<FuturesWarehouseReceipt(variety_code={self.variety_code}, stat_date={self.stat_date}, warehouse_name={self.warehouse_name})>"


class FuturesSpotPrice(BaseFinancialModel, PartitionMixin):
    """
    期货现货价格数据模型
    存储期货品种对应的现货价格信息
    """
    __tablename__ = "futures_spot_price"
    
    # 品种代码
    variety_code: Mapped[str] = mapped_column(String(20), nullable=False, comment="品种代码")
    
    # 价格日期
    price_date: Mapped[date] = mapped_column(Date, nullable=False, comment="价格日期")
    
    # 现货价格
    spot_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="现货价格")
    
    # 价格类型：平均价, 最高价, 最低价等
    price_type: Mapped[Optional[str]] = mapped_column(String(20), nullable=True, comment="价格类型")
    
    # 地区
    region: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, comment="地区")
    
    # 规格/品级
    specification: Mapped[Optional[str]] = mapped_column(String(200), nullable=True, comment="规格/品级")
    
    # 单位
    unit: Mapped[Optional[str]] = mapped_column(String(20), nullable=True, comment="单位")
    
    # 价格变化
    price_change: Mapped[Optional[Decimal]] = mapped_column(Numeric(12, 4), nullable=True, comment="价格变化")
    
    # 价格变化幅度(%)
    price_change_pct: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 4), nullable=True, comment="价格变化幅度(%)")
    
    # 数据来源
    data_source_detail: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, comment="数据来源详情")
    
    __table_args__ = (
        UniqueConstraint('variety_code', 'price_date', 'price_type', 'region', 'specification', 
                        name='uk_futures_spot_price_variety_date_type_region_spec'),
        Index('idx_futures_spot_price_variety', 'variety_code'),
        Index('idx_futures_spot_price_date', 'price_date'),
        Index('idx_futures_spot_price_type', 'price_type'),
        Index('idx_futures_spot_price_region', 'region'),
        # 分区索引
        Index('idx_futures_spot_price_partition_date', 'partition_date'),
    )
    
    def __repr__(self) -> str:
        return f"<FuturesSpotPrice(variety_code={self.variety_code}, price_date={self.price_date}, spot_price={self.spot_price})>"


# 导出所有期货相关模型
__all__ = [
    'FuturesBasicInfo',
    'FuturesDailyData',
    'FuturesMinuteData',
    'FuturesHoldingRank',
    'FuturesWarehouseReceipt',
    'FuturesSpotPrice',
]